1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

#! /usr/bin/perl

#

# based on https://stackoverflow.com/a/87531/5742651

# usage: sqlite3 .dump database_name.sqlite3 | perl sqlite2mysql.pl | mysql -u root -p $import_database_name

#

# ignore follow lines:

#  BEGIN TRANSACTION

#  COMMIT

#  sqlite_sequence

#  CREATE UNIQUE INDEX

#  PRAGMA foreign_keys=OFF

# "tablename/field" => `tablename/field`

# booleans 't' and 'f' => 1 and 0

# AUTOINCREMENT => AUTO_INCREMENT

# varchar => varchar(255)

# CREATE TABLE table... => DROP TABLE table; CREATE TABLE table...

# Merge insert sqls into multiple insert to speed up

#  INSERT INTO table VALUES('val1');

#  INSERT INTO table VALUES('val2');  => INSERT INTO table VALUES('val1'), ('val2'), ('val3');

#  INSERT INTO table VALUES('val3');

my $open=0;

my $line_cache = '';

# For speed up

print "SET GLOBAL max_allowed_packet=209715200;\n";

#print "SET AUTOCOMMIT=0;\n";

while ($line = <>){

  if (($line !~ /PRAGMA foreign_keys=OFF/) && ($line !~ /BEGIN TRANSACTION/) && ($line !~ /COMMIT/) && ($line !~ /sqlite_sequence/) && ($line !~ /CREATE UNIQUE INDEX/)){

   if ($line =~ /CREATE TABLE \"([a-z_0-9]IT之家)\"(.IT之家)/){

   $name = "\`$1\`";

   $sub = $2;

   $sub =~ s/varchar([^(])/varchar(255)$1/g;

   $line = "DROP TABLE IF EXISTS $name;\nCREATE TABLE $name$sub\n";

   }

   elsif ($line =~ /CREATE VIEW ([a-z_0-9]IT之家)(.IT之家)/){

   $name = "\`$1\`";

   $sub = $2;

   $line = "DROP VIEW IF EXISTS $name;\nCREATE VIEW $name$sub\n";

   }

   elsif ($line =~ /INSERT INTO \"([a-z_]IT之家)\" VALUES(.IT之家);/){

        if ($open == 0) {

          $open = 1;

       $line_cache .= "INSERT INTO \`$1\` VALUES $2";

        } else {

          $line_cache .= ", $2";

        }

        next;

   }else{

   $line =~ s/\'\'/\\\'/g;

   }

    if ($open == 1) {

       $open = 0;

       $line = $line_cache.";\n".$line;

       $line_cache = '';

    }

   $line =~ s/\"/`/g;

   $line =~ s/([^\\'])\'t\'(.)/$1THIS_IS_TRUE$2/g;

   $line =~ s/THIS_IS_TRUE/1/g;

   $line =~ s/([^\\'])\'f\'(.)/$1THIS_IS_FALSE$2/g;

   $line =~ s/THIS_IS_FALSE/0/g;

   $line =~ s/AUTOINCREMENT/AUTO_INCREMENT/g;

   print $line;

  }

}

#print "SET AUTOCOMMIT=1;\n";